package com.itheima;


import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

public class POIDemo {


    @Test
    public void poiRead() throws IOException {
        //创建工作簿对象
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook("C:\\Users\\Administrator\\Desktop\\2345.xlsx");
        //根据索引获取工作表对象
        XSSFSheet sheetAt = xssfWorkbook.getSheetAt(0);
        //获取所有的行
        int lastRowNum = sheetAt.getLastRowNum();
        System.out.println("lastRowNum" + lastRowNum);
        //遍历每一行
        for (int i = 0; i <= lastRowNum; i++) {
            XSSFRow row = sheetAt.getRow(i);
            System.out.println("row: " + row);

            short lastCellNum = row.getLastCellNum();
            System.out.println(lastCellNum);
            for (int j = 0; j < lastCellNum; j++) {
                XSSFCell cell = row.getCell(j);
                System.out.println("cell: " + cell.toString());
            }
        }
    }

    @Test
    public void poiRead2() throws IOException {
        //创建工作簿
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook("C:\\Users\\Administrator\\Desktop\\2345.xlsx");
        //根据工作表名获取工作表对象
        XSSFSheet sheet1 = xssfWorkbook.getSheet("Sheet1");
        //遍历对象
        for (Row row : sheet1) {
            for (Cell cell : row) {
                System.out.println(cell.toString());
                //单元格类型转换
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                }
                System.out.println("转换类型后的cell: " + cell.toString());
            }
        }
    }

    @Test
    public void poiWrite() throws IOException {
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        XSSFSheet testSheet = xssfWorkbook.createSheet("test");
        XSSFRow row1 = testSheet.createRow(0);
        XSSFCell cell1 = row1.createCell(0);
        cell1.setCellValue("666");
        XSSFCell cell2 = row1.createCell(1);
        cell2.setCellValue("777");

        XSSFRow row2 = testSheet.createRow(1);
        XSSFCell row2Cell1 = row2.createCell(0);
        row2Cell1.setCellValue("888");
        XSSFCell row2Cell2 = row2.createCell(1);
        row2Cell2.setCellValue("999");

        FileOutputStream outputStream = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\23456.xlsx");
        xssfWorkbook.write(outputStream);

        outputStream.flush();
        outputStream.close();
        xssfWorkbook.close();

    }

}
